package com.google.gac.masai.getit;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLhelper {
	public static final String KEY_NAME = "name";
    public static final String KEY_DATE = "date";
    public static final String KEY_EXTENSION = "extension";
    public static final String KEY_URI = "uri";
    public static String[] KEYS_OTHER_CLASSIFIERS = null;
    public static final String KEY_ROWID = "_id";
	
	private static String DATABASE_CREATE = "create table downloads " +
			"_id integer primary key autoincrement, name text not null," +
			"date text not null, extension text not null)";
	
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	
	private static final String DATABASE_NAME = "data";
    private static final String DATABASE_TABLE = "downloads";
    private static final int DATABASE_VERSION = 2;
    
    private Context mCtx;
	
	private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w("SQLhelper", "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS notes");
            onCreate(db);
        }
    }
	
	public SQLhelper(Context c) {
		this.mCtx = c;
	}
	
	/**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public SQLhelper open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        
        SharedPreferences others = mCtx.getSharedPreferences(mCtx.getClass().getName(), Context.MODE_PRIVATE);
        String temp = others.getString("SQLhelper_OTHER_CLASSIFIERS", null);
        KEYS_OTHER_CLASSIFIERS = temp.split(" ");
        
        return this;
    }

    public void close() {
        mDbHelper.close();
    }
    
    public void addClassifier(String newClassifier) {
    	String classifiers = "";
    	if(KEYS_OTHER_CLASSIFIERS!=null)
    		for(String s: KEYS_OTHER_CLASSIFIERS)
    			classifiers+=s;
    	classifiers+=newClassifier;
    	SharedPreferences others = mCtx.getSharedPreferences(mCtx.getClass().getName(), Context.MODE_PRIVATE);
    	SharedPreferences.Editor edit = others.edit();
    	edit.putString("SQLhelper_OTHER_CLASSIFIERS", classifiers);
    	edit.commit();
    	
    	mDb.execSQL("ALTER TABLE "+DATABASE_TABLE+" ADD "+newClassifier+" String;");
    }
    
    /**
     * Create a record of a newly downloaded file. If the record is
     * successfully created return the new rowId for that record, otherwise return
     * a -1 to indicate failure.
     * 
     * @param name the name of the file
     * @param date the date the file was downloaded
     * @param extension the file extension
     * @param URI a uri so that the file can be loaded
     * @return rowId or -1 if failed
     */
    public long addFile(String name, String date, String extension, String URI) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NAME, name);
        initialValues.put(KEY_DATE, date);
        initialValues.put(KEY_EXTENSION, extension);
        initialValues.put(KEY_URI, URI);
        
        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }
    
    /**
     * Create a record of a newly downloaded file. If the record is
     * successfully created return the new rowId for that record, otherwise return
     * a -1 to indicate failure.
     * 
     * @param name the name of the file
     * @param date the date the file was downloaded
     * @param others an array of the values for the other classifiers (must be the same length
     * as the number of other classifiers, use null if you don't have one)
     * @param URI a uri so that the file can be loaded
     * @return rowId or -1 if failed
     */
    public long addFile(String name, String date, String extension, String URI, String others[]) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NAME, name);
        initialValues.put(KEY_DATE, date);
        initialValues.put(KEY_EXTENSION, extension);
        initialValues.put(KEY_URI, URI);
        
        if(KEYS_OTHER_CLASSIFIERS != null) {
        	for(int i = 0; i< others.length; i++)
        		initialValues.put(KEYS_OTHER_CLASSIFIERS[i], others[i]);
        }
        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }
    
    public boolean removeFile(long rowID) {
    	return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowID, null) > 0;
    }
    
    public Cursor allFiles() {
    	return mDb.query(DATABASE_TABLE, new String[] {KEY_NAME, KEY_DATE, KEY_URI}, null, null, null, null, null);
    }
    
    /**
     * Gets a cursor over all the files with the given extension and other characteristics.
     * To accept all extensions or all files with a given extra characteristic, use null
     * as that string.  
     * 
     * @param extension the file extension
     * @param others an array of the other characteristics/tags
     * @return
     */
    public Cursor allFilesWith(String extension, String[] others) {
    	String selection ="";
    	boolean usedExt=false;
    	if(extension != null) {
    		selection += KEY_EXTENSION + "='"+extension+"'";
    		usedExt=true;
    	}
    	if(others!=null) {
    		for(int i = 0; i<others.length; i++) {
    			if(others[i]!=null) {
    				if(usedExt)
    					selection += " AND "+KEYS_OTHER_CLASSIFIERS[i]+"='"+others[i]+"'";
    				else {
    					selection += KEYS_OTHER_CLASSIFIERS[i]+"='"+others[i]+"'";
    					usedExt=true;
    				}
    			}
    		}
    	}
    	return mDb.query(DATABASE_TABLE, new String[] {KEY_NAME, KEY_DATE, KEY_URI}, selection, null, null, null, null);
    }
    
    
}
